<?php
/**
 * Created by PhpStorm.
 * User: LFL
 * Date: 2010/5/25
 * Time: 16:02
 */
namespace backend\controllers;

use Yii;
use yii\db\Exception;
use yii\web\AdminController;
use backend\models\Materiel;
use backend\models\GoodsBrand;
use backend\models\SysConfig;
use backend\models\Supplier;
use backend\models\Warehouse;
use backend\models\Shop;
use backend\models\GoodsCategory;
use backend\models\UploadForm;
use PHPExcel;
use common\helps\api_jd;

class MaterielController extends AdminController
{
    public $layout = false;
    //业务关键字
    public $service = 'materiel';

    public function actionIndex()
    {
        //仓库
        $warehouse_where = ['useFlg' => 1];
        $warehouse = Warehouse::find()->where($warehouse_where)->select('id,warehouseName')->asArray()->all();
        return $this->render('index', ['warehouse' => $warehouse]);
    }

    public function actionGetList()
    {
        $data = Yii::$app->request->post();
        $sql = '';
        $sql_list = 'SELECT ma.*,wh.warehouseName FROM j_materiel ma
        LEFT JOIN j_warehouse wh ON ma.warehouseId=wh.id WHERE 1=1';
        $sql_count = 'SELECT COUNT(0) CNT FROM j_materiel ma WHERE 1=1';

        $condition = $where = array();
        $searchName = trim($data['searchName']);
        if (!empty($searchName)) {
            $sql .= ' And ma.materielName like "%' . $searchName . '%"';
        }
        if (!empty($data['searchCode'])) {
            $sql .= ' And ma.materielCode like "%' . $data['searchCode'] . '%"';
        }
        if (isset($data['saleFlg']) && in_array($data['saleFlg'], array('0', '1'))) {
            $sql .= ' And ma.saleFlg = ' . $data['saleFlg'];
        }
        if (isset($data['useFlg']) && in_array($data['useFlg'], array('0', '1'))) {
            $sql .= ' And ma.useFlg = ' . $data['useFlg'];
        }
        if (isset($data['warehouseId']) && !empty($data['warehouseId'])) {
            $sql .= ' AND ma.warehouseId=' . $data['warehouseId'];
        }

        $list_count = Yii::$app->db->createCommand($sql_count . $sql)->queryOne();
        $count = $list_count['CNT'];
        $sort_string = empty($data['columns'][$data['order'][0]['column']]['data']) ? 'ma.id' : 'ma.' . $data['columns'][$data['order'][0]['column']]['data'];
        $sql .= ' ORDER BY ' . $sort_string . ' ' . ($data['order'][0]['dir'] == 'asc' ? 'asc' : 'desc');
        if ($data['start'] || $data['length']) {
            $sql .= ' limit ' . $data['start'] . ',' . $data['length'];
        }

        $list = Yii::$app->db->createCommand($sql_list . $sql)->queryAll();
        if (!empty($list)) {
            //存储类型
            $config_where = ['config_group_id' => 'storeType', 'use_flg' => 1];
            $storeType = SysConfig::find()->where($config_where)->select('config_key,config_value')->asArray()->all();
            $storeArr = array();
            foreach ($storeType as $s) {
                $storeArr[$s['config_key']] = $s['config_value'];
            }
            foreach ($list as &$val) {
                $val['storeName'] = isset($storeArr[$val['storeType']]) ? $storeArr[$val['storeType']] : '';
            }
        } else {
            $list = array();
        }

        //相关参数 http://datatables.club/manual/server-side.html
        $result['draw'] = intval($data['draw']);
        $result['recordsTotal'] = intval($count);
        $result['recordsFiltered'] = intval($count);
        $result['data'] = $list;
        //操作日志
        $this->saveLog($this->service, '物料查询', 'VIEW', '获取物料列表' . "\r\n" . \yii\helpers\Json::encode($list));
        return \yii\helpers\Json::encode($result);
    }

    public function actionDetail()
    {
        $data = Yii::$app->request->get();

        //一级分类列表
        $where = ['use_flg' => 1, 'level' => 1];
        $bigCategory = GoodsCategory::find()->where($where)->asArray()->all();

        //二级分类列表
        $midCategory = array();
        $where = ['use_flg' => 1, 'level' => 2];
        $result = GoodsCategory::find()->where($where)->asArray()->all();
        foreach ($result as $value) {
            $midCategory[$value['parent_id']][] = $value;
        }

        //三级分类列表
        $smallCategory = array();
        $where = ['use_flg' => 1, 'level' => 3];
        $result = GoodsCategory::find()->where($where)->asArray()->all();
        foreach ($result as $value) {
            $smallCategory[$value['parent_id']][] = $value;
        }

        //仓库
//        $warehouse_where = ['useFlg' => 1];
        $warehouse = Warehouse::find()->select('id,warehouseName')->asArray()->all();

        //供应商
        $supplier_where = ['useFlg' => 1];
        $supplier = Supplier::find()->where($supplier_where)->select('id,supplierName')->asArray()->all();

        //品牌
        $brand_where = ['use_flg' => 1];
        $brand = GoodsBrand::find()->where($brand_where)->select('id,brand_name')->asArray()->all();

        //存储类型
        $config_where = ['config_group_id' => 'storeType', 'use_flg' => 1];
        $storeType = SysConfig::find()->where($config_where)->select('config_key,config_value')->asArray()->all();

        //门店
        $shop_where = ['IN', 'Status', [1, 2]];
        $shop = Shop::find()->where($shop_where)->select('ShopID,CityID,DistrictID,ShopName,Address')->asArray()->all();

        $shopGroup = Yii::$app->db->createCommand("SELECT * FROM j_shop_group")->queryAll();

        $result = null;
        if ($data && isset($data['id'])) {
            $id = $data['id'];
//            $sql = 'SELECT ma.*,wh.warehouseName,sp.supplierName FROM j_materiel ma
//            LEFT JOIN j_warehouse wh ON ma.warehouseId=wh.id
//            LEFT JOIN j_supplier sp ON ma.supplierId=sp.id WHERE ma.id='. $id;
            $sql = 'SELECT * FROM j_materiel WHERE id=' . $id;
            $result = Yii::$app->db->createCommand($sql)->queryOne();
        }

        $model = new UploadForm();
        return $this->render('detail', [
            'bigCategory' => $bigCategory,
            'midCategory' => $midCategory,
            'smallCategory' => $smallCategory,
            'warehouse' => $warehouse,
            'supplier' => $supplier,
            'brand' => $brand,
            'storeType' => $storeType,
            'shop' => $shop,
            'shopGroup' => $shopGroup,
            'materiel' => $result,
            'model'=>$model
        ]);

    }

    public function actionAddMateriel()
    {
        $model = new Materiel();
        $data = Yii::$app->request->post();

        $materiel = array();
        $materiel['bigCategory'] = $data['bigCategory'];
        $materiel['midCategory'] = $data['midCategory'];
        $materiel['smallCategory'] = $data['smallCategory'];
        $materiel['materielName'] = $data['materielName'];
        $materiel['materielCode'] = $data['materielCode'];
        $materiel['warehouseId'] = $data['warehouseId'];
        $materiel['supplierId'] = $data['supplierId'];
        $materiel['brandId'] = $data['brandId'];
        $materiel['storeType'] = $data['storeType'];
        $materiel['specDesc'] = $data['specDesc'];
        $materiel['packDesc'] = $data['packDesc'];
        $materiel['purchaseUnit'] = $data['purchaseUnit'];
        $materiel['orderUnit'] = $data['orderUnit'];
        $materiel['convertUnit'] = $data['convertUnit'];
        $materiel['useUnit'] = $data['useUnit'];
        $materiel['useDesc'] = $data['useDesc'];
        $materiel['taxRate'] = $data['taxRate'];
        $materiel['safeDay'] = $data['safeDay'];
        $materiel['purchaseDay'] = $data['purchaseDay'];
        $materiel['referPrice'] = $data['referPrice'];
        $materiel['salePrice'] = $data['salePrice'];
        $materiel['saleFlg'] = $data['saleFlg'];
        $materiel['useFlg'] = $data['useFlg'];
        $materiel['storeFlg'] = $data['storeFlg'];
        $materiel['description'] = $data['description'];
        $materiel['sort'] = empty($data['sort']) ? 0 : $data['sort'];
        $materiel['shopGroupId'] = (isset($data['shopGroupId']) && !empty($data['shopGroupId'])) ? $data['shopGroupId'] : null;
        $materiel['safeStore'] = $data['safeStore'];


        try {
            $datetime = $data['datetime'];
            $datetime = explode(' - ', $datetime);

            $startTime = $datetime[0] . " 00:00:00";
            $endTime = $datetime[1] . " 23:59:59";
            $materiel['startTime'] = $startTime;
            $materiel['endTime'] = $endTime;

        } catch (Exception $ex) {
            $this->retJson('日期格式错误', 500);
        }


        if (isset($data['goods_main_pic']) && !empty($data['goods_main_pic'])) {
            $materiel['img'] = $data['goods_main_pic'];
        }

        if (empty($data['id'])) {
            $list_count = Yii::$app->db->createCommand('SELECT count(id) AS CNT FROM j_materiel WHERE warehouseId = ' . $materiel['warehouseId'] . ' AND materielCode = \'' . $materiel['materielCode'] . '\'')->queryOne();
            $count = $list_count['CNT'];
            if ($count > 0) {
                $this->retJson('添加失败,物料编号已存在', 500);
            }

            $materiel['creator'] = $model->getCreator();
            $materiel['create_time'] = date('Y-m-d H:i:s');
            $res = Yii::$app->db->createCommand()->insert('j_materiel', $materiel)->execute();
            $materielId = Yii::$app->db->getLastInsertId();
        } else {

            $list_count = Yii::$app->db->createCommand('SELECT count(id) AS CNT FROM j_materiel WHERE warehouseId = ' . $materiel['warehouseId'] . ' AND materielCode = \'' . $materiel['materielCode'] . '\'' . ' AND id !=' . $data['id'])->queryOne();
            $count = $list_count['CNT'];
            if ($count > 0) {
                $this->retJson('修改失败,物料编号已存在', 500);
            }

            $materielId = $data['id'];
            //修改参考价格记录日志
            $materielRes = $model::find()->select('referPrice')->where(['id' => $data['id']])->asArray()->one();
            if ($materielRes['referPrice'] !== $data['referPrice']) {
                $priceLog = array(
                    'creator' => $model->getCreator(),
                    'createTime' => date('Y-m-d H:i:s'),
                    'materielId' => $data['id'],
                    'old_val' => $materielRes['referPrice'],
                    'new_val' => $data['referPrice']
                );
                Yii::$app->db->createCommand()->insert('j_materiel_log', $priceLog)->execute();
            }

            $materiel['editor'] = $model->getCreator();
            $materiel['edit_time'] = date('Y-m-d H:i:s');
            $res = $model::updateAll($materiel, "id=:id", array(':id' => $data['id']));

        }
        if (!$res) {
            $this->retJson('操作失败', 500);
        }

        //同步金蝶
        $postData = array(
            'FNumber' => $materielId . '-' . $materiel['materielCode'],
            'FName' => $materiel['materielName'],   //名称
            'FModel' => $materiel['specDesc'],      //规格
            'FUnit' => $materiel['purchaseUnit'],   //计量单位
            'FClass' => $materiel['smallCategory'], //分类编码 <2021.03.07-lfl-只传3级>
            'FShelf' => empty($materiel['safeDay']) ? 0 : intval($materiel['safeDay']), //保质期
            'FStock' => $materiel['warehouseId'],           //仓库ID
            'FComBrandId_CMK' => $materiel['brandId'],      //品牌
            'FPurchaseUnitId' => $materiel['convertUnit'],  //采购单位编码
            'FTaxRateId' => $materiel['taxRate'],           //参考税率编码
            'FForbidStatus' => $materiel['useFlg'] == 1 ? true : false,         //禁用/启用
            'FIsEnableSafeStock' => $materiel['storeFlg'] == 1 ? true : false,  //是否管库存
            'Fdescription' => $materiel['description'],
            'FImage1' => isset($materiel['img']) ? $materiel['img'] : '',
            'FSafeStock' => empty($materiel['safeStore']) ? 0 : intval($materiel['safeStore']), //安全库存

            'FPackingMethod' => $materiel['packDesc'],      //包装方式描述
            'FOrderingUnit' => $materiel['orderUnit'],      //订货单位
            'FPuOrderUnit' => $materiel['convertUnit'],     //采购单位/订货单位
            'FMinOrderUnit' => $materiel['useUnit'],        //订货单位/最小使用单位
            'FProCurementCycle' => $materiel['purchaseDay'],//采购周期
            'FReferencePrice' => $materiel['referPrice'],   //参考价格
            'FSupplyPrice' => $materiel['convertUnit'],     //参考供货价格
            'FIsitOnSale' => $materiel['saleFlg'] == 1 ? 0 : 1, //是否上架  0上架 1未上架
            'FStrDate' => $materiel['startTime'],           //生效开始日期
            'FEndDate' => $materiel['endTime'],             //生效结束日期

        );
        $postData = array('data' => array($postData));
        api_jd::post('AddItem', $postData);

        $this->retJson('保存成功', 200);
    }


    //导出数据
    public function actionExport()
    {
        $data = Yii::$app->request->get();
        $sql = 'SELECT ma.*,wh.warehouseName FROM j_materiel ma
        LEFT JOIN j_warehouse wh ON ma.warehouseId=wh.id WHERE 1=1';

        if (isset($data['searchName']) && !empty($data['searchName'])) {
            $sql .= ' And ma.materielName like "%' . $data['searchName'] . '%"';
        }
        if (isset($data['searchCode']) && !empty($data['searchCode'])) {
            $sql .= ' And ma.materielCode like "%' . $data['searchCode'] . '%"';
        }
        if (isset($data['saleFlg']) && in_array($data['saleFlg'], array('0', '1'))) {
            $sql .= ' And ma.saleFlg = ' . $data['saleFlg'];
        }
        if (isset($data['useFlg']) && in_array($data['useFlg'], array('0', '1'))) {
            $sql .= ' And ma.useFlg = ' . $data['useFlg'];
        }
        if (isset($data['warehouseId']) && !empty($data['warehouseId'])) {
            $sql .= ' AND ma.warehouseId=' . $data['warehouseId'];
        }

        $list = Yii::$app->db->createCommand($sql)->queryAll();
        if (empty($list)) {
            $list = array();
        }

        $objPHPExcel = new PHPExcel();
        $objSheet = $objPHPExcel->getActiveSheet();//获取到sheet
        $objSheet->setTitle("物料".date('Ymd'));//设置sheet名字
        $objSheet->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置excel文件默认水平垂直方向居中
        $objSheet->getDefaultRowDimension()->setRowHeight(30);//设置默认行高
        $objSheet
            ->setCellValue("A1", '物料ID')
            ->setCellValue("B1", "物料名称")
            ->setCellValue("C1", "物料编号")
            ->setCellValue("D1", "所属仓库")
            ->setCellValue("E1", "规格描述")
            ->setCellValue("F1", "包装方式")
            ->setCellValue("G1", "采购单位")
            ->setCellValue("H1", "订货单位")
            ->setCellValue("I1", "采购单位/订货单位")
            ->setCellValue("J1", "订货单位/最小使用单位")
            ->setCellValue("K1", "最小使用单位说明")
            ->setCellValue("L1", "税率")
            ->setCellValue("M1", "质保天数")
            ->setCellValue("N1", "参考成本价")
            ->setCellValue("O1", "供货价")
            ->setCellValue("P1", "排序")
            ->setCellValue("Q1", "库存属性")
            ->setCellValue("R1", "安全库存")
            ->setCellValue("S1", "库存管理")
            ->setCellValue("T1", "销售状态")
            ->setCellValue("U1", "启用/禁用")
            ->setCellValue("V1", "门店");

        //存储类型
        $config_where = ['config_group_id' => 'storeType', 'use_flg' => 1];
        $storeType = SysConfig::find()->where($config_where)->select('config_key,config_value')->asArray()->all();
        $storeArr = array();
        foreach ($storeType as $s) {
            $storeArr[$s['config_key']] = $s['config_value'];
        }

        $i = 2;
        foreach ($list as $ma) {
            $ma['storeFlg'] = $ma['storeFlg'] ? '是' : '否';
            $ma['saleFlg'] = $ma['saleFlg'] ? '上架' : '下架';
            $ma['useFlg'] = $ma['useFlg'] ? '启用' : '禁用';
            $ma['storeName'] = isset($storeArr[$ma['storeType']]) ? $storeArr[$ma['storeType']] : '';
            $ma['shopName'] = '';

            if ($ma['shopGroupId']) {
                $sql_group = 'SELECT * FROM j_shop_group WHERE id IN (' . $ma['shopGroupId'] . ')';
            } else {
                $sql_group = 'SELECT * FROM j_shop_group';
            }



            $shop_groups = Yii::$app->db->createCommand($sql_group)->queryAll();
            $shopIds = [];
            foreach ($shop_groups as $group) {
                $tmp_groupIds = explode(',', $group['shopIds']);
                $shopIds = array_merge($shopIds, $tmp_groupIds);
            }


            $sql = 'select group_concat(ShopName) as shopName from j_shop where ShopID in (' . implode(',',$shopIds) . ')';
            $shop = Yii::$app->db->createCommand($sql)->queryOne();
            $ma['shopName'] = $shop ? $shop['shopName'] : '';


            $objSheet
                ->setCellValue("A$i", $ma['id'])
                ->setCellValue("B$i", $ma['materielName'])
                ->setCellValue("C$i", $ma['materielCode'])
                ->setCellValue("D$i", $ma['warehouseName'])
                ->setCellValue("E$i", ''.$ma['specDesc'])
                ->setCellValue("F$i", ''.$ma['packDesc'])
                ->setCellValue("G$i", ''.$ma['purchaseUnit'])
                ->setCellValue("H$i", ''.$ma['orderUnit'])
                ->setCellValue("I$i", ''.$ma['convertUnit'])
                ->setCellValue("J$i", ''.$ma['useUnit'])
                ->setCellValue("K$i", $ma['useDesc'])
                ->setCellValue("L$i", $ma['taxRate'])
                ->setCellValue("M$i", $ma['safeDay'])
                ->setCellValue("N$i", $ma['referPrice'])
                ->setCellValue("O$i", $ma['salePrice'])
                ->setCellValue("P$i", $ma['sort'])
                ->setCellValue("Q$i", $ma['storeName'])
                ->setCellValue("R$i", $ma['safeStore'])
                ->setCellValue("S$i", $ma['storeFlg'])
                ->setCellValue("T$i", $ma['saleFlg'])
                ->setCellValue("U$i", $ma['useFlg'])
                ->setCellValue("V$i", $ma['shopName']);
            $i++;
        }

        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(18);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(18);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(20);

        $objSheet->getStyle("A1:V1")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('6fc144');//填充背景颜色
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');//生成excel文件 如：Excel2007
        $fileName = 'data-' . time() . '.xls';
        $this->browser_export('Excel5', $fileName);//输出到浏览器 设置游览器保存文件名
        $objWriter->save("php://output");
    }

    function browser_export($type,$filename)
    {
        if ($type == "Excel5") {
            header('Content-Type: application/vnd.ms-excel');//告诉浏览器将要输出excel03文件
        } else {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器数据excel07文件
        }
        header('Content-Disposition: attachment;filename="' . $filename . '"');//告诉浏览器将输出文件的名称
        header('Cache-Control: max-age=0');//禁止缓存
    }
}